﻿using System;
using System.Collections.Generic;
using System.Text;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M010_BILL_SQL
    {
        #region SQL

        #region GET_SINGLE_DATA_SQL
        private static string _get_Single_Data_SQL = @"
            SELECT [CREATE_DATETIME]
                  ,[UPDATE_DATETIME]
                  ,[TRANS_STATE]
                  ,[UPDATER_ID]
                  ,[TERMINAL_CD]
                  ,[BILL_ID]
                  ,[GOODS_ID]
                  ,[RED_BLACK]
                  ,[IN_OUT]
                  ,[BUSINESS_TIME]
                  ,[MEMO]
            FROM   [M010_BILL]
            WHERE [BILL_ID] = @BILL_ID
            AND TRANS_STATE <> '3'";
        #endregion

        #region GET_LastStocktakingID_SQL
        private static string _get_LastStocktakingID_SQL = @"SELECT Top 1 M010.[BILL_ID]
,M009.STOREROOM_ID
FROM [M010_BILL] M010 left join M009_STORAGE M009 
On M009.BILL_ID = M010.BILL_ID
Where M010.IN_OUT = '2' 
Order By M010.UPDATE_DATETIME desc";
        #endregion

        #region Get Bill List

        private static string _get_Bill_List_SQL = @"Select distinct * from (
 SELECT M010.[CREATE_DATETIME]
	  ,M003.USER_NAME
      ,M010.[UPDATE_DATETIME]
      ,M010.[TRANS_STATE]
      ,M010.[UPDATER_ID]
      ,M010.[TERMINAL_CD]
      ,M010.[BILL_ID]
      ,CASE M010.[IN_OUT] WHEN '0' THEN '入库' WHEN '1' THEN '出库' when '2' then '盘库' END AS IN_OUT
      ,M010.[BUSINESS_TIME]
      ,M010.[MEMO]
      ,M008.STOREROOM_NM
      ,M008.STOREROOM_ID
FROM   [M010_BILL] M010 left join dbo.M003_USER M003 
On M003.USER_ID = M010.UPDATER_ID left join M009_STORAGE M009
On M009.BILL_ID = M010.BILL_ID left join M008_STOREROOM M008
On M008.STOREROOM_ID = M009.STOREROOM_ID
WHERE M010.TRANS_STATE <> '3' @@@ ) A";

        #endregion

        #region INSERT_BILL_SQL

        private static string _insert_BILL_SQL = @"INSERT INTO [M010_BILL](
                [CREATE_DATETIME]
               ,[UPDATE_DATETIME]
               ,[TRANS_STATE]
               ,[UPDATER_ID]
               ,[TERMINAL_CD]
               ,[BILL_ID]
               ,[IN_OUT]
               ,[BUSINESS_TIME]
               ,[MEMO]
               ,[USER_NO]
               ,[Preferential]
            )VALUES(
                CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
               ,CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
               ,@TRANS_STATE
               ,@UPDATER_ID
               ,@TERMINAL_CD
               ,@BILL_ID
               ,@IN_OUT
               ,@BUSINESS_TIME
               ,@MEMO
               ,@USER_NO,@Preferential)";

        #endregion

        #endregion

        #region Proprety

        public static string Get_Single_Data_SQL
        {
            get
            {
                return _get_Single_Data_SQL;
            }
        }

        public static string Insert_BILL_SQL
        {
            get
            {
                return _insert_BILL_SQL;
            }
        }

        public static string GetBillList_SQL
        {
            get { return _get_Bill_List_SQL; }
        }

        public static string Get_LastStocktakingID_SQL
        {
            get { return _get_LastStocktakingID_SQL; }
        }

        #endregion
    }
}
